Release 10.1A: OpenEdge Data Management:
DataServer for ORACLE

RDBMS stored procedure basics

In the OpenEdge environment, you can think of a stored procedure definition as having two basic, interrelated parts:

Execution controls to run and close a store procedure — Comprise the information needed to execute a stored procedure request against the ORACLE data source. At a minimum, all stored procedures discussed in this guide are assessable using the RUN STORED-PROCEDURE statement.
Language elements that enable access to specific data results — Qualify the retrieved data, or result set, that the stored procedure’s execution returns. Various keywords, phrases, statements, and syntax elements support different retrieval options for stored procedure output. This part of the stored procedure implementation reflects your analysis of your data needs; based on this analysis, you determine the additional syntax elements you need to define the output elements and data results you want retrieved.

Table 3–1 identifies and briefly introduces the elements that comprise a stored procedure definition; each of these elements is also more fully discussed later in this chapter.

Table 3–1: Stored procedure language elements

Progress 4GL	Description
RUN STORED–PROCEDURE statement	Executes a stored procedure.
PROC–HANDLE phrase	Allows you to specify a handle to identify a stored procedure.
PROC–STATUS phrase	Reads the return value.
LOAD-RESULT-INTO phrase	Allows data from a result set that is returned for a foreign data source to be put into one or more temp-tables.
PARAM phrase	Identifies run-time parameters to be passed to and/or from the stored procedure.
CLOSE STORED–PROCEDURE statement	Enables the values to be retrieved from the output parameters that you defined for the stored procedure, finalizes result sets data processing, and tells Progress that the stored procedure has ended.

Note: You can substitute the abbreviations RUN STORED–PROC and CLOSE STORED–PROC for the full names RUN STORED–PROCEDURE and CLOSE STORED–PROCEDURE, respectively. The remainder of this guide generally uses the abbreviated form.

See the "Run Stored-Procedure details" section for more details about the reference entries presented in Table 3–1.

As previously noted in Table 3–1, you can pass data types in the RUN STORED-PROCEDURE statement using the PARAM phrase. Table 3–2 lists issues that occur when you pass certain data types as parameters.

Table 3–2: Argument data types for stored procedures 

OpenEdge	ORACLE data source
DECIMAL FLOAT INTEGER	The DataServer represents all three data types as the OpenEdge INTEGER data type in the schema image. To preserve the scale and precision of these data types, you must manually update the information in the schema image for these parameters.Use the OpenEdge Data Dictionary to update the data type and format information in the Field Property Sheet for the parameter.
VARCHAR2	In ORACLE, VARCHAR2 parameters cannot be greater than 4000 characters. If the VARCHAR2 parameter exceeds this limit, it causes an error. Note: If you use a parameter that is larger than 255 characters, you need to change the _for-maxsize value for this parameter.
DATE	If you pass a DATE data type as an input parameter and use it in an equality test, the test might fail. In this case, use the trunc function in the stored procedure to isolate parts of the date structure for which you might want to test. For example: procedure x_date (indate in date, outdate out date) as begin select date_terminate into outdate from datetbl      where trunc(hire_date) = trunc (indate); end;

Note these stored procedure points:

Input and output parameters are displayed as fields.
If you are running several stored procedures, run them serially and process all the results from one stored procedure and close the procedure before you run a second one. By default, the DataServer allows one active request for running a stored procedure. It is not necessary to specify the PROC-HANDLE phrase when procedures are run serially.
When you run stored procedures concurrently, the DataServer uses one connection to the data source per procedure. If different stored procedures attempt to update the same record from a single client’s requests, the connections could block each other or a deadlock might occur.
Note: You must define a PROC-HANDLE phrase for each stored procedure phrase that is simultaneously active. This technique provides a CLOSE STORED-PROC statement that can identify the targeted open procedure and close it.

In contrast, since a stored procedure executed with the LOAD-RESULT-INTO phrase implicitly closes the procedure once the execution ends and the data retrieved is placed into temp tables, it essentially runs serially and has no use for a PROC-HANDLE.
When you create or update your schema image, the stored procedures appear in the list of accessible objects along with tables, view, and sequences. OpenEdge allows you to run the stored procedures that you create in data sources using the procedure definitions in your schema image. See your ORACLE documentation for complete information about creating and using stored procedures.
If the 4GL that executes a stored procedure is already within a transaction block, the stored procedure becomes an extension of that transaction and will not commit to the database until the 4GL transaction is completed. However, because the stored procedure does not execute as part of the 4GL client process, it cannot be rolled back by the 4GL.
The DataServer cannot roll back sub-transactions in the stored-procedure context since it has no control over what the stored procedure executes.

The "Run Stored-Procedure details" section presents more details about the use of the RUN STORED-PROC statement.